
** Clean up FX rates
import excel "./DataSkeleton/short_basis_raw_07082021.xlsx", sheet("Sheet1") firstrow clear
local cid AUD CAD EUR CHF JPY GBP
foreach i of local cid{
rename `i'F080Curncy spot_`i'
rename `i'ONF080Curncy fwdon_`i'
rename `i'TNF080Curncy fwdtn_`i'
rename `i'SNF080Curncy fwdsn_`i'
}
drop if Date==.
destring spot* fwd*,force replace
rename Date date
reshape long spot_ fwdon_ fwdtn_ fwdsn_, i(date) j(currency) string
save "./OutputInterim/Qend/raw_fx_rates.dta",replace


** Clean up dates
import excel "./DataSkeleton/short_basis_raw_07082021.xlsx", sheet("Sheet2") firstrow clear
rename Date date
drop B
local cid AUD CAD EUR CHF JPY GBP
foreach i of local cid{
gen date_spot_`i'=date(`i'Curncy,"MDY")
gen date_on_`i'=date(`i'ONCurncy,"MDY")
gen date_tn_`i'=date(`i'TNCurncy,"MDY")
gen date_sn_`i'=date(`i'SNCurncy,"MDY")
}
format date_* %td
keep date date*
reshape long date_spot_ date_on_ date_tn_ date_sn_, i(date) j(currency) string
mmerge date currency using "./OutputInterim/Qend/raw_fx_rates.dta", unmatched(master)
drop _merge
sort date
sort currency date
save "./OutputInterim/Qend/raw_fx_rates_dates.dta",replace



** Clean up interest rates
import excel "./DataSkeleton/short_basis_raw_07082021.xlsx", sheet("Sheet3") firstrow clear
rename IRR ior_USD
rename EUO ior_EUR
rename UK ior_GBP
rename RBACTRDIndex ior_AUD
rename CABROVERIndex ior_CAD
rename Dates date
destring ior_*, force replace
gen ior_JPY=0.1 if date<td(29jan2016)
replace ior_JPY=-0.1 if date>=td(29jan2016)
drop RBA
gen ior_CHF=0
replace ior_CHF=-0.25 if date>=td(18dec2014)
replace ior_CHF=-0.75 if date>=td(15jan2015)
rename ior_USD ioer
reshape long ior_, i(date) j(currency) string
rename ioer usdior
rename ior_ forior
mmerge date currency using  "./OutputInterim/Qend/raw_fx_rates_dates.dta", unmatched(using)
drop _merge
sort date
keep if spot!=. & fwdon!=.
save "./OutputInterim/Qend/raw_short_basis_clean.dta",replace

**** Calculate basis
use "./OutputInterim/Qend/raw_short_basis_clean.dta",clear
gen n_on=date_on-date
gen n_tn=date_tn-date_on
gen n_sn=date_sn-date_spot
*** JPY
gen basis_on=100*(((1+forior/100*n_on/360)/(spot-fwdtn/100)*(spot-fwdon/100-fwdtn/100)-1)*100*360/n_on-usdior) if currency=="JPY"
gen basis_tn=100*(((1+forior/100*n_tn/360)/spot*(spot-fwdtn/100)-1)*100*360/n_tn-usdior) if currency=="JPY"
gen basis_sn=100*(((1+forior/100*n_sn/360)*spot/(spot+fwdsn/100)-1)*100*360/n_sn-usdior) if currency=="JPY"
*** CHF
replace basis_on=100*(((1+forior/100*n_on/360)/(spot-fwdtn/10000)*(spot-fwdon/10000-fwdtn/10000)-1)*100*360/n_on-usdior) if currency=="CHF"
replace basis_tn=100*(((1+forior/100*n_tn/360)/spot*(spot-fwdtn/10000)-1)*100*360/n_tn-usdior) if currency=="CHF"
replace basis_sn=100*(((1+forior/100*n_sn/360)*spot/(spot+fwdsn/10000)-1)*100*360/n_sn-usdior) if currency=="CHF"
*** GBP/EUR/AUD
replace basis_on=100*(((1+forior/100*n_on/360)*(spot-fwdtn/10000)/(spot-fwdon/10000-fwdtn/10000)-1)*100*360/n_on-usdior) if inlist(currency,"GBP","AUD","EUR")
replace basis_tn=100*(((1+forior/100*n_tn/360)*spot/(spot-fwdtn/10000)-1)*100*360/n_tn-usdior) if inlist(currency,"GBP","AUD","EUR")
replace basis_sn=100*(((1+forior/100*n_sn/360)/spot*(spot+fwdsn/10000)-1)*100*360/n_sn-usdior) if inlist(currency,"GBP","AUD","EUR")
*** CAD
replace basis_on=100*(((1+forior/100*n_on/360)/(spot-fwdtn/10000)*(spot-fwdon/10000-fwdtn/10000)-1)*100*360/n_on-usdior) if currency=="CAD"
replace basis_tn=100*(((1+forior/100*n_tn/360)*spot/(spot+fwdtn/10000)-1)*100*360/n_tn-usdior) if currency=="CAD"
replace basis_sn=100*(((1+forior/100*n_sn/360)*spot/(spot+fwdsn/10000)-1)*100*360/n_sn-usdior) if currency=="CAD"
*** in theory CAD T/N and S/N should be the same, but they are a bit different
keep date date_* currency basis_*
save "./OutputInterim/Qend/short_basis_clean.dta",replace



*---------------------------
* Doing analysis 
*---------------------------


** Generate a list of "good dates" 
use "./OutputInterim/Qend/short_basis_clean.dta",clear
keep date_spot currency 
duplicates drop 
sort date_spot 
save "./OutputInterim/Qend/spot_date.dta",replace

*** Only keep the quotes on good spot days
use "./OutputInterim/Qend/short_basis_clean.dta",clear
mmerge date currency using "./OutputInterim/Qend/spot_date.dta", umatch(date_spot currency) unmatched(none)
sort currency date
drop _merge
save "./OutputInterim/Qend/short_basis.dta",replace

*----------------------------
*** Run regressions 
*----------------------------




use "./OutputInterim/Qend/short_basis.dta",clear
gen date1=date
format date1 %td
mmerge date1 date_on currency using "./OutputInterim/Qend/short_basis.dta", ///
ukeep(basis_tn date) umatch(date_on date_tn currency) unmatched(master) uname(lag_)
sort date
drop _merge
gen diff_tnon=lag_basis_tn-basis_on
drop if year(date)>=2021
mmerge date using "./DataSkeleton/period_end_v2.dta", ukeep(qe) unmatched(master)
replace qe=0 if _merge==1
replace qe=1 if inlist(date,td(30jun2020),td(30sep2020),td(31dec2020))
drop _merge
sort date

gen qe2015=qe*(year(date)>=2015)
gen y2015=(year(date)>=2015)
*drop if inlist(currency,"AUD")
drop if inlist(currency,"AUD","CAD","GBP")
gen post_crisis=(date>=td(1jul2010))
gen post_sample=(date>td(31jul2018))
*** Pooled regression (2008/10/10)
keep if diff_tnon!=. & date!=td(3jan2017)
reg basis_on qe if post_crisis==1, r
outreg2 using "./OutputFinal/reg_ontn.xls",  replace

reg lag_basis_tn qe if post_crisis==1, r
outreg2 using "./OutputFinal/reg_ontn.xls"

reg diff_tnon qe if post_crisis==1, r
outreg2 using "./OutputFinal/reg_ontn.xls"

